﻿//
// (C) Copyright 2009 Irantha Suwandarathna (iranthas@hotmail.com)
// All rights reserved.
//

/********************************************************
 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 * 
 ********************************************************/

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Globalization;
using System.Reflection;
using EffiProzDB;
using EffiProzDB.DataTypes;

namespace System.Data.EffiProzDB
{
    public class EffiProzDBDataReader : DbDataReader
    {
        /// <summary>
        /// Underlying command this reader is attached to
        /// </summary>
        private EffiProzDBCommand _command;

        /** The internal representation. */
        public Result rResult;

        /**
         * The current record containing the data for the row
         */
        private Record nCurrent;

        /** The row upon which this ResultSet is currently positioned. */
        private int iCurrentRow;

        /** When the result of updating the database, the number of updated rows. */
        private int iUpdateCount;

        /** Is current row before the first row? */
        private bool bInit;    // false if before first row

        /** How many columns does this ResultSet have? */
        int iColumnCount;

        /// <summary>
        /// State of the current statement being processed.
        /// -1 = First Step() executed, so the first Read() will be ignored
        ///  0 = Actively reading
        ///  1 = Finished reading
        ///  2 = Non-row-returning statement, no records
        /// </summary>
        private int _readingState;
      
        /** The ResultSetMetaData object for this ResultSet */
       // private ResultSetMetaData rsmd;

        /** Properties of this ResultSet's parent Connection. */
       // private HsqlProperties connProperties;

        /** is the connection via network */
       // private bool isNetConn;

        internal long _version; // Matches the version of the connection

        /// <summary>
        /// The behavior of the datareader
        /// </summary>
        private CommandBehavior _commandBehavior;

        /// <summary>
        /// If set, then dispose of the command object when the reader is finished
        /// </summary>
        internal bool _disposeCommand;
   

        /** The maximum number of rows to generate when executing this statement. */
       // protected int maxRows = 0;

       /** Used by this statement to communicate non-batched requests. */
        protected Result resultOut = new Result(ResultConstants.SQLEXECDIRECT);

        /** Use by this statement to communicate batched execution requests */
        protected Result batchResultOut = null;


        /// <summary>
        /// Internal constructor, initializes the datareader and sets up to begin executing statements
        /// </summary>
        /// <param name="cmd">The EffiProzDBCommand this data reader is for</param>
        /// <param name="behave">The expected behavior of the data reader</param>
        internal EffiProzDBDataReader(EffiProzDBCommand cmd, CommandBehavior behave)
        {
            _command = cmd;
            _version = _command.Connection._version;

            _commandBehavior = behave;
            iUpdateCount = -1;

            if (_command != null)
                NextResult();
        }
     
  
        internal void Cancel()
        {
            _version = 0;
        }



        /// <summary>
        /// Closes the datareader, potentially closing the connection as well if CommandBehavior.CloseConnection was specified.
        /// </summary>
        public override void Close()
        {
            iUpdateCount = -1;
            

            if (_command != null)
            {
                try
                {
                    try
                    {
                        // Make sure we've not been canceled
                        if (_version != 0)
                        {
                            try
                            {
                                while (NextResult())
                                {
                                }
                            }
                            catch (EffiProzDBException)
                            {
                            }
                        }
                        _command.ClearDataReader();
                    }
                    finally
                    {
                        // If the datareader's behavior includes closing the connection, then do so here.
                        if ((_commandBehavior & CommandBehavior.CloseConnection) != 0 && _command.Connection != null)
                            _command.Connection.Close();
                    }
                }
                finally
                {
                    if (_disposeCommand)
                        _command.Dispose();
                }

                _command = null;
                rResult = null;

            }
        }
        
     

        /// <summary>
        /// Throw an error if the datareader is closed
        /// </summary>
        private void CheckClosed()
        {
            if ( _command == null)
                throw new InvalidOperationException("DataReader has been closed");

            if (_version == 0)
                throw new EffiProzDBException( "Execution was aborted by the user");

            if (_command.Connection.State != ConnectionState.Open || _command.Connection._version != _version)
                throw new InvalidOperationException("Connection was closed, statement was terminated");
        }

        /// <summary>
        /// Throw an error if a row is not loaded
        /// </summary>
        private void CheckValidRow()
        {


            if (_readingState != 0) //if (rResult == null || !bInit || nCurrent == null)
            {
                throw EffiProzDBException.GetException(Trace.NO_DATA_IS_AVAILABLE);
            }
        }

        /// <summary>
        /// Enumerator support
        /// </summary>
        /// <returns>Returns a DbEnumerator object.</returns>
        public override Collections.IEnumerator GetEnumerator()
        {
            return new DbEnumerator(this, ((_commandBehavior & CommandBehavior.CloseConnection) == CommandBehavior.CloseConnection));
        }

        /// <summary>
        /// Not implemented.  Returns 0
        /// </summary>
        public override int Depth
        {
            get
            {
                CheckClosed();
                return 0;
            }
        }

        /// <summary>
        /// Returns the number of columns in the current resultset
        /// </summary>
        public override int FieldCount
        {
            get
            {
                CheckClosed();

                return  iColumnCount;
            }
        }

        /// <summary>
        /// Returns the number of visible fielsd in the current resultset
        /// </summary>
        public override int VisibleFieldCount
        {
            get
            {
                CheckClosed();
                return iColumnCount;
            }
        }

        private object ConvertToType(int i, DbType typ)
        {
            CheckClosed();
            CheckValidRow();

            int internalType =  rResult.metaData.colTypes[i];
            object value = nCurrent.data[i];

            if (internalType == Types.OTHER) // variant type in EffiProzDB
            {
                DotNetObject proxy = (DotNetObject)value;
                value = proxy.getObject();
            }

            switch (typ)
            {
                case DbType.Boolean:
                    if (internalType == Types.BOOLEAN || internalType == Types.OTHER) return Convert.ToBoolean(value);
                    break;
                case DbType.Byte :
                    if (internalType == Types.TINYINT || internalType == Types.OTHER) return Convert.ToByte(value);              
                    break;
                case DbType.SByte:
                    if (internalType == Types.TINYINT || internalType == Types.OTHER) return Convert.ToChar(value);
                    break;
                case DbType.Int16:
                    if (internalType == Types.SMALLINT || internalType == Types.OTHER) return Convert.ToInt16(value);
                    break;
                case DbType.Int32:
                    if (internalType == Types.INTEGER || internalType == Types.OTHER) return Convert.ToInt32(value);
                    break;
                case DbType.Int64:
                    if (internalType == Types.BIGINT || internalType == Types.OTHER) return Convert.ToInt64(value);
                    break;
                case DbType.Single:
                    if (internalType == Types.FLOAT || internalType == Types.OTHER) return Convert.ToSingle(value);
                    break;
                case DbType.Double:
                    if (internalType == Types.DOUBLE || internalType == Types.OTHER) return Convert.ToDouble(value);
                    if (internalType == Types.REAL ) return Convert.ToDouble(value);
                    break;
                case DbType.Decimal:
                    if (internalType == Types.DECIMAL || internalType == Types.OTHER) return Convert.ToDecimal(value);
                    if (internalType == Types.NUMERIC) return Convert.ToDecimal(value);
                    break;
                case DbType.String:
                    if (internalType == Types.VARCHAR || internalType == Types.OTHER) return Convert.ToString(value);
                    if (internalType == Types.CHAR ) return Convert.ToString(value);
                    if (internalType == Types.LONGVARCHAR ) return Convert.ToString(value);
                    if (internalType == Types.VARCHAR_IGNORECASE ) return Convert.ToString(value);
                    break;
                case DbType.DateTime:
                case DbType.Date:
                case DbType.Time:
                    if (internalType == Types.DATE || internalType == Types.OTHER) return Convert.ToDateTime(value);
                    if (internalType == Types.TIME ) return Convert.ToDateTime(value);
                    if (internalType == Types.TIMESTAMP ) return Convert.ToDateTime(value);
                    break;
                case DbType.Binary:
                    if (internalType == Types.BINARY || internalType == Types.OTHER) return value;
                    if (internalType == Types.VARBINARY ) return value;
                    if (internalType == Types.LONGVARBINARY ) return value;   
                    break;
                case DbType.Object:
                    if (internalType == Types.OTHER) return value;                  
                    break;              
            }

            throw new InvalidCastException();
        }

        /// <summary>
        /// Retrieves the column as a boolean value
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>bool</returns>
        public override bool GetBoolean(int i)
        {
            CheckNull(i);
            object val = ConvertToType(i, DbType.Boolean);
            return (bool)val;
        }

        /// <summary>
        /// Retrieves the column as a single byte value
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>byte</returns>
        public override byte GetByte(int i)
        {
            CheckNull(i);
            object val = ConvertToType(i, DbType.Byte);
            return (byte)val;
        }

        /// <summary>
        /// Retrieves a column as an array of bytes (blob)
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <param name="fieldOffset">The zero-based index of where to begin reading the data</param>
        /// <param name="buffer">The buffer to write the bytes into</param>
        /// <param name="bufferoffset">The zero-based index of where to begin writing into the array</param>
        /// <param name="length">The number of bytes to retrieve</param>
        /// <returns>The actual number of bytes written into the array</returns>
        /// <remarks>
        /// To determine the number of bytes in the column, pass a null value for the buffer.  The total length will be returned.
        /// </remarks>
        public override long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.Binary);
            Binary b = (Binary)val;
            if (fieldOffset > b.getBytesLength())
                return 0;
            length = length > (b.getBytesLength() - fieldOffset)? length : (b.getBytesLength() -(int) fieldOffset);
            if (length <= 0)
                return 0;

            Array.Copy(b.getBytes(), (int)fieldOffset, buffer, bufferoffset,length);
            return length;
        }


        /// <summary>
        /// Returns the column as a single character
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>char</returns>
        public override char GetChar(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.SByte);
            return (char)val;
        }

        /// <summary>
        /// Retrieves a column as an array of chars (blob)
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <param name="fieldoffset">The zero-based index of where to begin reading the data</param>
        /// <param name="buffer">The buffer to write the characters into</param>
        /// <param name="bufferoffset">The zero-based index of where to begin writing into the array</param>
        /// <param name="length">The number of bytes to retrieve</param>
        /// <returns>The actual number of characters written into the array</returns>
        /// <remarks>
        /// To determine the number of characters in the column, pass a null value for the buffer.  The total length will be returned.
        /// </remarks>
        public override long GetChars(int i, long fieldOffset, char[] buffer, int bufferoffset, int length)
        {
            CheckNull(i);

            string val = (string)ConvertToType(i, DbType.String);          

            if (fieldOffset > val.Length)
                return 0;
            length = length > (val.Length - fieldOffset) ? length : (val.Length - (int)fieldOffset);
            if (length <= 0)
                return 0;

            val.CopyTo((int)fieldOffset, buffer, bufferoffset, length);

            return length;
        }

        /// <summary>
        /// Retrieves the name of the back-end datatype of the column
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>string</returns>
        public override string GetDataTypeName(int i)
        {
            //CheckNull(i);

            CheckClosed();
            //CheckValidRow();
            int internalType = rResult.metaData.colTypes[i];
            return Types.getTypeName(internalType);
        }

        /// <summary>
        /// Retrieve the column as a date/time value
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>DateTime</returns>
        public override DateTime GetDateTime(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.DateTime);
            return (DateTime)val;
        }

        /// <summary>
        /// Retrieve the column as a decimal value
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>decimal</returns>
        public override decimal GetDecimal(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.Decimal);
            return (decimal)val;
        }

        /// <summary>
        /// Returns the column as a double
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>double</returns>
        public override double GetDouble(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.Double);
            return (double)val;
        }

        /// <summary>
        /// Returns the .NET type of a given column
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>Type</returns>
        public override Type GetFieldType(int i)
        {
            CheckClosed();            
            int internalType = rResult.metaData.colTypes[i];

            switch (internalType)
            {
                case Types.BOOLEAN:
                    return typeof(bool);
              
                case Types.TINYINT:
                    return typeof(byte);
                  
                case Types.SMALLINT:
                    return typeof(short);
            
                case Types.INTEGER:
                    return typeof(int);
           
                case Types.BIGINT:
                    return typeof(long);
           
                case Types.FLOAT:
                    return typeof(float);
           
                case Types.DOUBLE:
                case Types.REAL:
                    return typeof(double);
     
                case Types.DECIMAL:
                case Types.NUMERIC:
                    return typeof(decimal);
        
                case Types.VARCHAR:
                case Types.VARCHAR_IGNORECASE:
                case Types.CHAR:
                case Types.LONGVARCHAR:
                    return typeof(string);
                
                case Types.DATE:
                case Types.TIME:
                case Types.TIMESTAMP:
                    return typeof(DateTime);
             
                case Types.BINARY:
                case Types.VARBINARY:
                case Types.LONGVARBINARY:
                    return typeof(byte[]);
        
                case Types.OTHER:
                    return typeof(object);
             
            }

            throw new InvalidCastException();
        
        }

        /// <summary>
        /// Returns a column as a float value
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>float</returns>
        public override float GetFloat(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.Single);
            return (Single)val;
        }

        /// <summary>
        /// Returns the column as a Guid
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>Guid</returns>
        public override Guid GetGuid(int i)
        {
            throw new InvalidCastException();
        }

        /// <summary>
        /// Returns the column as a short
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>Int16</returns>
        public override Int16 GetInt16(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.Int16);
            return (short)val;
        }

        /// <summary>
        /// Retrieves the column as an int
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>Int32</returns>
        public override Int32 GetInt32(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.Int32);
            return (int)val;
        }

        /// <summary>
        /// Retrieves the column as a long
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>Int64</returns>
        public override Int64 GetInt64(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.Int64);
            return (long)val;
        }

        /// <summary>
        /// Retrieves the name of the column
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>string</returns>
        public override string GetName(int columnIndex)
        {
            string name;
            try
            {
                name = rResult.metaData.colNames[columnIndex];
            }
            catch (IndexOutOfRangeException)
            {
                throw EffiProzDBException.GetException(Trace.COLUMN_NOT_FOUND, (columnIndex).ToString());
            }

            return name;
        }

        /// <summary>
        /// Retrieves the i of a column, given its name
        /// </summary>
        /// <param name="name">The name of the column to retrieve</param>
        /// <returns>The int i of the column</returns>
        public override int GetOrdinal(string columnName)
        {
            CheckClosed();

            for (int i = 0; i < iColumnCount; i++)
            {
                String name = rResult.metaData.colLabels[i];

                if (columnName.Equals(name,StringComparison.InvariantCultureIgnoreCase))
                {
                    return i;
                }
            }

            throw new IndexOutOfRangeException(columnName);
        }


        public override DataTable GetSchemaTable()
        {          
            CheckClosed();

            if (rResult == null || !rResult.isData())
            {
                throw EffiProzDBException.GetException(Trace.GENERAL_ERROR,
                                        Trace.ADO_NO_RESULT_SET, null);
            }

            DataTable tbl = new DataTable("SchemaTable");          
            DataRow row;       
         
            Result.ResultMetaData rmd = rResult.metaData;
            int type;
            int precision;
     
            tbl.Locale = CultureInfo.InvariantCulture;
            tbl.Columns.Add(SchemaTableColumn.ColumnName, typeof(String));
            tbl.Columns.Add(SchemaTableColumn.ColumnOrdinal, typeof(int));
            tbl.Columns.Add(SchemaTableColumn.ColumnSize, typeof(int));
            tbl.Columns.Add(SchemaTableColumn.NumericPrecision, typeof(int));
            tbl.Columns.Add(SchemaTableColumn.NumericScale, typeof(short));
            tbl.Columns.Add(SchemaTableColumn.IsUnique, typeof(Boolean));
            tbl.Columns.Add(SchemaTableColumn.IsKey, typeof(Boolean));
            tbl.Columns.Add(SchemaTableOptionalColumn.BaseCatalogName, typeof(String));
            tbl.Columns.Add(SchemaTableColumn.BaseColumnName, typeof(String));
            tbl.Columns.Add(SchemaTableColumn.BaseSchemaName, typeof(String));
            tbl.Columns.Add(SchemaTableColumn.BaseTableName, typeof(String));
            tbl.Columns.Add(SchemaTableColumn.DataType, typeof(Type));
            tbl.Columns.Add(SchemaTableColumn.AllowDBNull, typeof(Boolean));
            tbl.Columns.Add(SchemaTableColumn.ProviderType, typeof(int));
            tbl.Columns.Add(SchemaTableColumn.IsAliased, typeof(Boolean));
            tbl.Columns.Add(SchemaTableColumn.IsExpression, typeof(Boolean));
            tbl.Columns.Add(SchemaTableOptionalColumn.IsAutoIncrement, typeof(Boolean));
            tbl.Columns.Add(SchemaTableColumn.IsLong, typeof(Boolean));
            tbl.Columns.Add(SchemaTableOptionalColumn.IsReadOnly, typeof(Boolean));
            tbl.Columns.Add(SchemaTableOptionalColumn.DefaultValue, typeof(string));

            DataTable indexTbl = null;
            DataTable pkTbl = null;
            DataTable columnTbl = null;

            if ((_commandBehavior & CommandBehavior.KeyInfo) != 0) // provide all  available information
            {//Anyway this key Info will be invalid for multi-table select... just use first column table/schema

                indexTbl = _command.Connection.GetSchema("INDEXCOLUMNS", new string[] {null, rmd.schemaNames[0], rmd.tableNames[0],
                    null,null,"true" });
                pkTbl = _command.Connection.GetSchema("PRIMARYKEYS", new string[] { null, rmd.schemaNames[0], rmd.tableNames[0] });
                columnTbl = _command.Connection.GetSchema("COLUMNS", new string[] { null, rmd.schemaNames[0], rmd.tableNames[0] });
            }


            tbl.BeginLoadData();

            for (int n = 0; n < iColumnCount; n++)
            {
                row = tbl.NewRow();

                // Default settings for the column
                if (rmd.colLabels[n] == null)
                    row[SchemaTableColumn.ColumnName] = DBNull.Value;
                else
                    row[SchemaTableColumn.ColumnName] = rmd.colLabels[n];

                row[SchemaTableColumn.ColumnOrdinal] = n;
                row[SchemaTableColumn.ColumnSize] = rmd.colSizes[n] == 0 ? int.MaxValue : rmd.colSizes[n];
                row[SchemaTableColumn.ProviderType] = rmd.colTypes[n];
                type = rmd.colTypes[n];

                if (Types.isNumberType(type)
                    && Types.acceptsPrecisionCreateParam(type))
                {
                    precision = rmd.colSizes[n];

                    if (precision == 0)
                    {
                        precision = Types.getPrecision(type);
                    }
                }
                else
                {
                    precision = Types.getPrecision(type);
                }
                row[SchemaTableColumn.NumericPrecision] = precision;

                if (Types.acceptsScaleCreateParam(type))
                {
                    row[SchemaTableColumn.NumericScale] = rmd.colScales[n];
                }
                else
                    row[SchemaTableColumn.NumericScale] = DBNull.Value;

                row[SchemaTableColumn.IsLong] = rmd.colTypes[n] == Types.LONGVARBINARY || rmd.colTypes[n] == Types.LONGVARCHAR;

                row[SchemaTableColumn.AllowDBNull] =  rmd.colNullable[n];
                row[SchemaTableOptionalColumn.IsReadOnly] = !rmd.isWritable[n];
                row[SchemaTableOptionalColumn.IsAutoIncrement] = rmd.isIdentity[n];

                row[SchemaTableColumn.IsUnique] = false;
                row[SchemaTableColumn.IsKey] = false;
                row[SchemaTableColumn.DataType] = GetFieldType(n);

           
                if (rmd.colNames[n] == null)
                    row[SchemaTableColumn.BaseColumnName] = DBNull.Value;
                else
                    row[SchemaTableColumn.BaseColumnName] = rmd.colNames[n];

                row[SchemaTableColumn.IsExpression] = (rmd.colNames[n] == null) ;

                row[SchemaTableColumn.IsAliased] = (String.Compare(rmd.colNames[n], rmd.colLabels[n], StringComparison.OrdinalIgnoreCase) != 0);

          
                if (rmd.tableNames[n] == null)
                    row[SchemaTableColumn.BaseTableName] = DBNull.Value;
                else
                    row[SchemaTableColumn.BaseTableName] = rmd.tableNames[n];
                

                if (rmd.catalogNames[n] == null)
                    row[SchemaTableOptionalColumn.BaseCatalogName] = DBNull.Value;
                else
                    row[SchemaTableOptionalColumn.BaseCatalogName] = rmd.catalogNames[n];
                
                if (rmd.schemaNames[n] == null)
                    row[SchemaTableColumn.BaseSchemaName] = DBNull.Value;
                else
                    row[SchemaTableColumn.BaseSchemaName] = rmd.schemaNames[n] ;


                if ((_commandBehavior & CommandBehavior.KeyInfo) != 0)
                {
                    for (int i = 0; i < pkTbl.Rows.Count; i++)
                    {
                        if (rmd.colNames[n].Equals((string)pkTbl.Rows[i]["COLUMN_NAME"], StringComparison.OrdinalIgnoreCase))
                        {
                            row[SchemaTableColumn.IsKey] = true;
                        }
                    }

                    for (int i = 0; i < indexTbl.Rows.Count; i++)
                    {
                        if (rmd.colNames[n].Equals((string)indexTbl.Rows[i]["COLUMN_NAME"], StringComparison.OrdinalIgnoreCase))
                        {
                            row[SchemaTableColumn.IsUnique] = true;
                        }
                    }

                    for (int i = 0; i < columnTbl.Rows.Count; i++)
                    {
                        if (rmd.colNames[n].Equals((string)columnTbl.Rows[i]["COLUMN_NAME"], StringComparison.OrdinalIgnoreCase))
                        {
                            row[SchemaTableOptionalColumn.DefaultValue] = columnTbl.Rows[i]["COLUMN_DEF"]==null?
                                DBNull.Value : columnTbl.Rows[i]["COLUMN_DEF"];
                        }
                    }
                }


                tbl.Rows.Add(row);
            }

            tbl.AcceptChanges();
            tbl.EndLoadData();

            return tbl;
        }

        /// <summary>
        /// Retrieves the column as a string
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>string</returns>
        public override string GetString(int i)
        {
            CheckNull(i);

            object val = ConvertToType(i, DbType.String);
            return (string)val;
        }

        /// <summary>
        /// Retrieves the column as an object corresponding to the underlying datatype of the column
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>object</returns>
        public override object GetValue(int columnIndex)
        {
            CheckValidRow();
            return nCurrent == null ? DBNull.Value : (nCurrent.data[columnIndex] == null ? DBNull.Value :
                ConvertToTypeSpecialCases(rResult.metaData.colTypes[columnIndex], nCurrent.data[columnIndex]));
        }

        private object ConvertToTypeSpecialCases(int internalType, object val)
        {
          
            switch (internalType)
            {                
                case Types.TINYINT:
                    return Convert.ToByte(val);              
                case Types.SMALLINT:
                    return Convert.ToInt16(val);                       
                case Types.FLOAT:
                    return Convert.ToSingle(val);                        
                case Types.BINARY:
                case Types.VARBINARY:
                case Types.LONGVARBINARY:
                    Binary bin = (Binary)val;
                    return bin.getClonedBytes();                
                case Types.OTHER:
                    DotNetObject obj = (DotNetObject)val;
                    return obj.getObject();          
            }

            return val;

        }

        /// <summary>
        /// Retreives the values of multiple columns, up to the size of the supplied array
        /// </summary>
        /// <param name="values">The array to fill with values from the columns in the current resultset</param>
        /// <returns>The number of columns retrieved</returns>
        public override int GetValues(object[] values)
        {
            CheckValidRow();

            int nMax = iColumnCount;
            if (values.Length < nMax) nMax = values.Length;

            for (int n = 0; n < nMax; n++)
            {
                values[n] = GetValue(n);
            }

            return nMax;
        }

        /// <summary>
        /// Returns True if the resultset has rows that can be fetched
        /// </summary>
        public override bool HasRows
        {
            get
            {
                CheckClosed();
                return (_readingState != 1 && _readingState != 2);
                //if (iCurrentRow == rResult.getSize() + 1)
                //    return false;
                //else
                //    return true;
            }
        }

        /// <summary>
        /// Returns True if the data reader is closed
        /// </summary>
        public override bool IsClosed
        {
            get { return (_command == null); }
        }

        /// <summary>
        /// Returns True if the specified column is null
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>True or False</returns>
        public override bool IsDBNull(int i)
        {
            object val = GetValue(i);
            return val == DBNull.Value;
        }

        /// <summary>
        /// Moves to the next resultset in multiple row-returning SQL command.
        /// </summary>
        /// <returns>True if the command was successful and a new resultset is available, False otherwise.</returns>
        public override bool NextResult()
        {

            if (rResult != null)
                return false;

            CheckClosed();

            FetchResult();

            if (rResult.mode == ResultConstants.UPDATECOUNT)
            {
                iUpdateCount = rResult.getUpdateCount();
                _readingState = 2;
            }
            else if (rResult.isError())
            {
                EffiProzDBException.throwError(rResult);
            }
            else if (rResult.isData())
            {
                iUpdateCount = -1;
                iColumnCount = rResult.getColumnCount();
                _readingState = 0;
            }
            else
            {
                _readingState = 2;
            }

           
            return true;            
        }

        public void CheckNull(int i)
        {
            if(IsDBNull(i))
                throw new ArgumentNullException();
        }


        /// <summary>
        /// Reads the next row from the resultset
        /// </summary>
        /// <returns>True if a new row was successfully loaded and is ready for processing</returns>
        public override bool Read()
        {
            CheckClosed();

            // Have an empty resultset so exit with false
            if (rResult == null || rResult.isEmpty())
            {
                _readingState = 1;
                return false;
            }

            if (!bInit)
            {
                // The resultset has not been traversed, so set the cursor
                // to the first row (1)
                nCurrent = rResult.rRoot;
                bInit = true;
                iCurrentRow = 1;
            }
            else
            {
                if ((_commandBehavior & CommandBehavior.SingleRow) == 0)
                {
                    // The resultset has been traversed, if afterLast, return false
                    if (nCurrent == null)
                    {
                        return false;
                    }

                    // On a valid row so go to next
                    nCurrent = nCurrent.next;

                    iCurrentRow++;
                }
                else
                    nCurrent = null;
            }

            // finally test to see if we are in an afterLast situation
            if (nCurrent == null)
            {

                // Yes, set the current row to after last and exit with false
                iCurrentRow = rResult.getSize() + 1;
                _readingState = 1; // Finished reading rows
                return false;
            }
            else
            {
                // Not afterLast, so success
                return true;
            }       
      
        }

        /// <summary>
        /// Retrieve the count of records affected by an update/insert command.  Only valid once the data reader is closed!
        /// </summary>
        public override int RecordsAffected
        {
            get { return (iUpdateCount < 0) ? 0 : iUpdateCount; }
        }

        /// <summary>
        /// Indexer to retrieve data from a column given its name
        /// </summary>
        /// <param name="name">The name of the column to retrieve data for</param>
        /// <returns>The value contained in the column</returns>
        public override object this[string name]
        {
            get { return GetValue(GetOrdinal(name)); }
        }

        /// <summary>
        /// Indexer to retrieve data from a column given its i
        /// </summary>
        /// <param name="i">The index of the column to retrieve</param>
        /// <returns>The value contained in the column</returns>
        public override object this[int i]
        {
            get { return GetValue(i); }
        }

        /**
       * Internal result producer for command (sqlExecDirect mode). <p>
       *
       * @param sql a character sequence representing the SQL to be executed
       * @throws EffiProzDBException when a database access error occurs
       */
        private void FetchResult()
        {

            if (_command.Parameters.Count != 0 && _command._preparedStmt == null)
                _command.Prepare();

            if (_command._preparedStmt != null)
            {
                rResult = _command._preparedStmt.Execute(_command.Parameters, _command.MaxRows);
                return;
            }
        

            String sql = _command.CommandText;
            rResult = null;

            resultOut.setMainString(sql);
            resultOut.setMaxRows(_command.MaxRows);

            try
            {
                rResult = _command.Connection.sessionProxy.execute(resultOut);

                if (rResult.isError())
                {
                    throw new HsqlException(rResult);
                }
              
            }
            catch (HsqlException e)
            {
                throw EffiProzDBException.GetException(e);
            }

           
        }

    }
}
